﻿using SharpDB.SPI;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Collections;
using System.Data;
using System.Data.Common;

namespace SharpDB
{
    /// <summary>
    /// 数据库 操作
    /// Database Operation
    /// </summary>
    public partial class DAOBase : IDAO
    {
        /// <summary>
        /// 数据提供程序工厂
        /// </summary>
        public DbProviderFactory ProviderFactory { get; private set; }

        /// <summary>
        /// 数据库连接字符串
        /// </summary>
        public string ConnectionString { get; private set; }

        /// <summary>
        /// 参数化字符（不同数据库参数化字符可能不同。）
        /// </summary>
        public string ParamCharacter { get; private set; }

        /// <summary>
        /// 异常出错时 执行事件
        /// </summary>
        public event EventHandler<OnErrorEventArgs> OnError;

        private DBInfo _dbInfo = null;

        /// <summary>
        /// 当前数据库相关信息
        /// </summary>
        public DBInfo Info
        {
            get
            {
                if (!CheckStruct)
                {
                    throw new ArgumentException("CheckStruct未开启！");
                }
                return _dbInfo;
            }
            set
            {
                this._dbInfo = value;
            }
        }

        private bool _checkStruct = true;

        /// <summary>
        /// 是否开启 表列结构检查(默认开启)
        /// 开启结构检查，则可以避免某些情况的异常。如：给定的列与数据库中的表列不对应 而进行操作。
        /// 关闭结构检查，可以避免因 对表列结构的 检查所造成的性能损失。
        /// </summary>
        public bool CheckStruct
        {
            get { return _checkStruct; }
            private set { _checkStruct = value; }
        }

        /// <summary>
        /// 数据库类型
        /// </summary>
        public DBType DbType { get; private set; }

        public DAOBase(DbProviderFactory providerFactory, DBType dBType, string connectionString, string paramCharacter, bool checkStruct = true)
        {
            this.ProviderFactory = providerFactory;
            this.ConnectionString = connectionString;
            this.CheckStruct = checkStruct;
            this.DbType = dBType;
            this.ParamCharacter = paramCharacter;
            if (this.CheckStruct)
            {
                this.Info = new DBInfo(this);
            }
        }

        #region Ado.Net 对象

        private string _Database = string.Empty;

        /// <summary>
        /// 当前操作的数据库名称
        /// </summary>
        public string Database
        {
            get { return _Database; }
        }
        internal DbConnection CreateConn()
        {
            return CreateConn(this.ConnectionString);
        }

        internal DbConnection CreateConn(string connectionString)
        {
            DbConnection _DBConn = ProviderFactory.CreateConnection();
            if (!string.IsNullOrWhiteSpace(connectionString))
            {
                _DBConn.ConnectionString = connectionString;
            }
            else
            {
                _DBConn.ConnectionString = this.ConnectionString;
            }
            _Database = _DBConn.Database;
            return _DBConn;
        }

        internal DbCommand CreateCmd()
        {
            DbCommand _DBCmd = ProviderFactory.CreateCommand();
            return _DBCmd;
        }

        internal DbCommand CreateCmd(string commandText = null, DbConnection DbConn = null)
        {
            DbCommand _DBCmd = ProviderFactory.CreateCommand();
            if (DbConn != null)
            {
                _DBCmd.Connection = DbConn;
            }
            if (!string.IsNullOrWhiteSpace(commandText))
            {
                _DBCmd.CommandText = commandText;
            }
            return _DBCmd;
        }


        internal DbDataAdapter CreateAdapter(DbCommand dbCmd = null)
        {
            DbDataAdapter dbadapter = ProviderFactory.CreateDataAdapter();
            if (dbCmd != null)
            {
                dbadapter.SelectCommand = dbCmd;
            }
            return dbadapter;
        }
        internal DbDataAdapter CreateAdapter(string commandText, DbConnection DbConn = null)
        {
            DbDataAdapter dbadapter = ProviderFactory.CreateDataAdapter();
            if (!string.IsNullOrWhiteSpace(commandText))
            {
                dbadapter.SelectCommand = CreateCmd(commandText, DbConn);
            }
            return dbadapter;
        }

        internal DbParameter CreateParameter()
        {
            DbParameter dbparameter = ProviderFactory.CreateParameter();
            return dbparameter;
        }

        #endregion


        /// <summary>
        /// 尝试连接
        /// </summary>
        /// <returns></returns>
        public bool TryConnect()
        {
            DbConnection connection = null;
            try
            {
                connection = CreateConn();
                connection.Open();
                return true;
            }
            catch
            {
                return false;
            }
            finally
            {
                if (connection != null)
                {
                    connection.Close();
                }
            }
        }

        public bool Exists(string strSql, params System.Data.IDataParameter[] cmdParms)
        {
            object obj = GetSingle<object>(strSql, 30, cmdParms);
            int cmdresult;
            if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
            {
                cmdresult = 0;
            }
            else
            {
                cmdresult = int.Parse(obj.ToString());
            }
            if (cmdresult == 0)
            {
                return false;
            }
            else
            {
                return true;
            }
        }

        #region 单值、DataRow、DataTable、List<DataTable>、DbDataReader 返回
        public TReturn GetSingle<TReturn>(string strSql, int times = 30, params System.Data.IDataParameter[] cmdParms)
        {
            using (DbConnection connection = CreateConn())
            {
                using (DbCommand cmd = CreateCmd())
                {
                    try
                    {
                        PrepareCommand(cmd, connection, null, strSql, cmdParms, times);
                        object obj = cmd.ExecuteScalar();
                        return obj.ChangeType<TReturn>();
                    }
                    catch (Exception ex)
                    {
                        this.OnError?.Invoke(this, new OnErrorEventArgs(new string[] { cmd.CommandText }, cmd.Parameters, ex));
                        throw ex;
                    }
                }
            }
        }

        public DataRow QueryRow(string strSql, int times = 30, params System.Data.IDataParameter[] cmdParms)
        {
            DataTable data = QueryDS(strSql, times, cmdParms).FirstOrDefault();
            if (data != null && data.Rows.Count > 0)
            {
                return data.Rows[0];
            }
            return null;
        }

        public DataTable QueryTable(string strSql, int times = 30, params System.Data.IDataParameter[] cmdParms)
        {
            return QueryDS(strSql, times, cmdParms).FirstOrDefault();
        }

        public List<DataTable> QueryDS(string strSql, int times = 30, params System.Data.IDataParameter[] cmdParms)
        {
            using (DbConnection connection = CreateConn())
            {
                DbCommand cmd = connection.CreateCommand();
                PrepareCommand(cmd, connection, null, strSql, cmdParms, times);
                using (DbDataAdapter da = CreateAdapter(cmd))
                {
                    List<DataTable> lstTab = new List<DataTable>();
                    DataSet ds = new DataSet();
                    try
                    {
                        da.Fill(ds, "ds");
                        cmd.Parameters.Clear();
                        if (ds != null && ds.Tables.Count > 0)
                        {
                            foreach (DataTable table in ds.Tables)
                            {
                                lstTab.Add(table);
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        this.OnError?.Invoke(this, new OnErrorEventArgs(new string[] { cmd.CommandText }, cmd.Parameters, ex));
                        throw ex;
                    }
                    return lstTab;
                }
            }
        }

        public DataTable QueryTableByProc(string storedProcName, int times = 30, params IDataParameter[] cmdParms)
        {
            DataSet ds = QueryDSByProc(storedProcName, times, cmdParms);
            if (ds != null && ds.Tables.Count > 0)
            {
                return ds.Tables[0];
            }
            return null;
        }

        public DataSet QueryDSByProc(string storedProcName, int times = 30, params IDataParameter[] cmdParms)
        {
            DataSet ds = new DataSet("ds");
            using (DbConnection conn = CreateConn())
            {
                conn.Open();
                DbCommand cmd = CreateCmd();
                try
                {
                    PrepareCommand(cmd, conn, null, storedProcName, cmdParms, times, CommandType.StoredProcedure);
                    DataAdapter adapter = CreateAdapter(cmd);
                    adapter.Fill(ds);
                    return ds;
                }
                catch (Exception ex)
                {
                    this.OnError?.Invoke(this, new OnErrorEventArgs(new string[] { cmd.CommandText }, cmd.Parameters, ex));
                    throw ex;
                }
            }
        }

        public DbDataReader ExecuteReader(string strSql, int times = 30, params System.Data.IDataParameter[] cmdParms)
        {
            DbConnection connection = CreateConn();
            DbCommand cmd = CreateCmd();
            try
            {
                PrepareCommand(cmd, connection, null, strSql, cmdParms, times);
                DbDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                return myReader;
            }
            catch (Exception ex)
            {
                this.OnError?.Invoke(this, new OnErrorEventArgs(new string[] { cmd.CommandText }, cmd.Parameters, ex));
                throw ex;
            }
        }

        #endregion



        #region 插入、更新、删除 执行命令/执行存储过程
        public int ExecuteSql(string strSql, int times = 30, params System.Data.IDataParameter[] cmdParms)
        {
            using (DbConnection connection = CreateConn())
            {
                using (DbCommand cmd = CreateCmd())
                {
                    try
                    {
                        PrepareCommand(cmd, connection, null, strSql, cmdParms, times);
                        int cnt = cmd.ExecuteNonQuery();
                        cmd.Parameters.Clear();
                        return cnt;
                    }
                    catch (Exception ex)
                    {
                        this.OnError?.Invoke(this, new OnErrorEventArgs(new string[] { cmd.CommandText }, cmd.Parameters, ex));
                        throw ex;
                    }
                }
            }
        }

        public int ExecuteSqlTran(params string[] sqlCmds)
        {
            if (sqlCmds == null || sqlCmds.Length == 0)
            {
                return -1;
            }
            using (DbConnection conn = CreateConn())
            {
                conn.Open();
                DbCommand cmd = CreateCmd();
                cmd.Connection = conn;
                DbTransaction tran = conn.BeginTransaction();
                cmd.Transaction = tran;

                List<string> lstSql = null;
                //List<DbParameterCollection> lstParam = null;
                if (this.OnError != null)
                {
                    lstSql = new List<string>();
                    //lstParam = new List<DbParameterCollection>();
                }
                try
                {
                    int cnt = 0;
                    for (int n = 0; n < sqlCmds.Length; n++)
                    {
                        string strsql = sqlCmds[n];
                        if (strsql.Trim().Length > 1)
                        {
                            cmd.CommandText = strsql;
                            if (this.OnError != null)
                            {
                                lstSql.Add(cmd.CommandText);
                                //lstParam.Add(cmd.Parameters);
                            }
                            cnt += cmd.ExecuteNonQuery();
                        }
                    }
                    tran.Commit();
                    return cnt;
                }
                catch (Exception ex)
                {
                    this.OnError?.Invoke(this, new OnErrorEventArgs(lstSql.ToArray(), null, ex));
                    tran.Rollback();
                    return -1;
                }
            }
        }


        public int ExecuteSqlTran(Hashtable SqlCmdList)
        {
            using (DbConnection conn = CreateConn())
            {
                conn.Open();
                using (DbTransaction trans = conn.BeginTransaction())
                {
                    DbCommand cmd = CreateCmd();
                    List<string> lstSql = null;
                    List<DbParameterCollection> lstParam = null;
                    if (this.OnError != null)
                    {
                        lstSql = new List<string>();
                        lstParam = new List<DbParameterCollection>();
                    }
                    try
                    {
                        int count = 0;
                        //循环
                        foreach (DictionaryEntry myDE in SqlCmdList)
                        {
                            string cmdText = myDE.Key.ToString();
                            IDataParameter[] cmdParms = ((List<IDataParameter>)myDE.Value).ToArray();
                            PrepareCommand(cmd, conn, trans, cmdText, cmdParms);
                            if (this.OnError != null)
                            {
                                lstSql.Add(cmd.CommandText);
                                lstParam.Add(cmd.Parameters);
                            }
                            count += cmd.ExecuteNonQuery();
                            cmd.Parameters.Clear();
                        }
                        trans.Commit();
                        return count;
                    }
                    catch (Exception ex)
                    {
                        this.OnError?.Invoke(this, new OnErrorEventArgs(lstSql.ToArray(), lstParam, ex));
                        trans.Rollback();
                        return -1;
                    }
                }
            }
        }

        public int ExecuteProc(string procName, int times = 30, params IDataParameter[] cmdParms)
        {
            using (DbConnection connection = CreateConn())
            {
                using (DbCommand cmd = CreateCmd())
                {
                    try
                    {
                        PrepareCommand(cmd, connection, null, procName, cmdParms, times, CommandType.StoredProcedure);
                        int cnt = cmd.ExecuteNonQuery();
                        cmd.Parameters.Clear();
                        return cnt;
                    }
                    catch (Exception ex)
                    {
                        this.OnError?.Invoke(this, new OnErrorEventArgs(new string[] { cmd.CommandText }, cmd.Parameters, ex));
                        throw ex;
                    }
                }
            }
        }

        #endregion

        #region private

        private void PrepareCommand(DbCommand cmd, DbConnection conn, DbTransaction trans, string cmdText, IDataParameter[] cmdParms, int times = 30, CommandType cmdType = CommandType.Text)
        {
            if (conn.State != ConnectionState.Open)
                conn.Open();
            cmd.Connection = conn;
            cmd.CommandText = cmdText;
            if (trans != null)
                cmd.Transaction = trans;
            cmd.CommandType = cmdType;
            if (times > 0)//（以秒为单位）。默认为 30 秒
            {
                cmd.CommandTimeout = times;
            }
            else
            {
                cmd.CommandTimeout = 30;
            }
            if (cmdParms != null && cmdParms.Length > 0)
            {
                foreach (IDataParameter parameter in cmdParms)
                {
                    if ((parameter.Direction == ParameterDirection.InputOutput
                        || parameter.Direction == ParameterDirection.Input)
                        && (parameter.Value == null))
                    {
                        parameter.Value = DBNull.Value;
                    }
                    cmd.Parameters.Add(parameter);
                }
            }
        }

        #endregion



        #region 未实现方法
        public virtual bool ValidateSql(string strSql, out string msg)
        {
            throw new NotImplementedException();
        }

        public virtual void BulkCopy(DataTable data, string tableName, Dictionary<string, string> columnMappings = null, int batchSize = 200000, int bulkCopyTimeout = 60)
        {
            throw new NotImplementedException();
        }

        public virtual void BulkCopy(DbDataReader reader, string tableName, Dictionary<string, string> columnMappings = null, int batchSize = 200000, int bulkCopyTimeout = 60)
        {
            throw new NotImplementedException();
        }


        #endregion
    }
}
